﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PurchaseOrderDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PurchaseOrderDAL()
        {

        }

        public IList<PurchaseOrderInfo> GetPurchaseOrder(Nullable<int> YearTypeSeq, Nullable<int> Year, Nullable<int> PurchaseMethodSeq
            , Nullable<int> PurchaseTypeSeq, Nullable<int> OperationNameSeq, Nullable<int> Status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInfo> infoList = new List<PurchaseOrderInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@no_status", SystemInfo._NoStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + " SELECT po.ICM_PO_SEQ"
                        + " , pr.ICM_PC_TYPE_SEQ"
                        + " , pt.ICM_PC_TYPE_NAME"
                        + " , po.ORDER_DATE"
                        + " , po.ICM_PO_REF_NO"
                        + " , po.ICM_PO_NAME"
                        + " , po.CAN_INSTALLMENT"
                        + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                        + " , rs.RECORD_STATUS_NAME"
                        + " , po.YEAR_TYPE_SEQ"
                        + " , po.BGM_YEAR"
                        + " , po.ICM_PO_TYPE_SEQ" 
                        + " , pr.ICM_PC_METHOD_SEQ"
                        + " , po.RECORD_STATUS" 
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID bid ON bid.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bid.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON po.RECORD_STATUS = rs.RECORD_STATUS"
                        + " UNION ALL"
                        + " SELECT po.ICM_PO_SEQ"
                        + " , pr.ICM_PC_TYPE_SEQ"
                        + " , pt.ICM_PC_TYPE_NAME"
                        + " , po.ORDER_DATE"
                        + " , po.ICM_PO_REF_NO"
                        + " , po.ICM_PO_NAME"
                        + " , po.CAN_INSTALLMENT"
                        + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                        + " , rs.RECORD_STATUS_NAME"
                        + " , po.YEAR_TYPE_SEQ"
                        + " , po.BGM_YEAR"
                        + " , po.ICM_PO_TYPE_SEQ"
                        + " , pr.ICM_PC_METHOD_SEQ"
                        + " , po.RECORD_STATUS" 
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON po.RECORD_STATUS = rs.RECORD_STATUS"
                        + ") main"
                        + " WHERE ICM_PO_TYPE_SEQ = 1"
                            + " AND CAN_INSTALLMENT = @no_status"
                            + " AND RECORD_STATUS <> @delete_status";
                strSQL += (YearTypeSeq == null) ? "" : " and YEAR_TYPE_SEQ = " + YearTypeSeq;
                strSQL += (Year == null) ? "" : " and BGM_YEAR = " + Year;
                strSQL += (PurchaseMethodSeq == null) ? "" : " and ICM_PC_METHOD_SEQ = " + PurchaseMethodSeq;
                strSQL += (PurchaseTypeSeq == null) ? "" : " and ICM_PC_TYPE_SEQ = " + PurchaseTypeSeq;
                strSQL += (Status == null) ? "" : " and RECORD_STATUS = '" + Status + "'";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInfo info = new PurchaseOrderInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                        MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                        MasterContactInfo infoContact = new MasterContactInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_po_seq"]);
                        infoPcType.Seq = Convert.ToInt32(dr[i]["icm_pc_type_seq"]);
                        infoPcType.Name = dr[i]["icm_pc_type_name"].ToString();
                        info.Date = (Convert.IsDBNull(dr[i]["order_date"]) ? null : (Nullable<DateTime>)(dr[i]["order_date"]));
                        info.Ref_No = dr[i]["icm_po_ref_no"].ToString();
                        info.Name = dr[i]["icm_po_name"].ToString();
                        infoContact.Name_th = dr[i]["contact_name"].ToString();
                        infoRecordStatus.Name = dr[i]["record_status_name"].ToString();

                        infoPr.InfoPurchaseType = infoPcType;
                        infoBidder.InfoPurchaseRequest = infoPr;
                        infoBidder.InfoMasContact = infoContact;
                        info.InfoBidder = infoBidder;
                        info.InfoRecordStatus = infoRecordStatus;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInfo> GetPurchaseOrderInstallment(Nullable<int> YearTypeSeq, Nullable<int> Year, Nullable<int> PurchaseMethodSeq
            , Nullable<int> PurchaseTypeSeq, Nullable<int> OperationNameSeq, Nullable<int> Status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInfo> infoList = new List<PurchaseOrderInfo>(); ;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + " SELECT po.ICM_PO_SEQ"
                        + " , pr.ICM_PC_TYPE_SEQ"
                        + " , pt.ICM_PC_TYPE_NAME"
                        + " , po.ORDER_DATE"
                        + " , po.ICM_PO_REF_NO"
                        + " , po.ICM_PO_NAME"
                        + " , po.CAN_INSTALLMENT"
                        + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                        + " , rs.RECORD_STATUS_NAME"
                        + " , po.YEAR_TYPE_SEQ"
                        + " , po.BGM_YEAR"
                        + " , po.ICM_PO_TYPE_SEQ"
                        + " , pr.ICM_PC_METHOD_SEQ"
                        + " , po.RECORD_STATUS"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID bid ON bid.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bid.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON po.RECORD_STATUS = rs.RECORD_STATUS"
                        + " UNION ALL"
                        + " SELECT po.ICM_PO_SEQ"
                        + " , pr.ICM_PC_TYPE_SEQ"
                        + " , pt.ICM_PC_TYPE_NAME"
                        + " , po.ORDER_DATE"
                        + " , po.ICM_PO_REF_NO"
                        + " , po.ICM_PO_NAME"
                        + " , po.CAN_INSTALLMENT"
                        + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                        + " , rs.RECORD_STATUS_NAME"
                        + " , po.YEAR_TYPE_SEQ"
                        + " , po.BGM_YEAR"
                        + " , po.ICM_PO_TYPE_SEQ"
                        + " , pr.ICM_PC_METHOD_SEQ"
                        + " , po.RECORD_STATUS"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON po.RECORD_STATUS = rs.RECORD_STATUS"
                        + ") main"
                        + " WHERE ICM_PO_TYPE_SEQ = 1"
                            + " AND CAN_INSTALLMENT = @yes_status"
                            + " AND RECORD_STATUS <> @delete_status";
                strSQL += (YearTypeSeq == null) ? "" : " and YEAR_TYPE_SEQ = " + YearTypeSeq;
                strSQL += (Year == null) ? "" : " and BGM_YEAR = " + Year;
                strSQL += (PurchaseMethodSeq == null) ? "" : " and ICM_PC_METHOD_SEQ = " + PurchaseMethodSeq;
                strSQL += (PurchaseTypeSeq == null) ? "" : " and ICM_PC_TYPE_SEQ = " + PurchaseTypeSeq;
                strSQL += (Status == null) ? "" : " and RECORD_STATUS = '" + Status + "'";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInfo info = new PurchaseOrderInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                        MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                        MasterContactInfo infoContact = new MasterContactInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_po_seq"]);
                        infoPcType.Seq = Convert.ToInt32(dr[i]["icm_pc_type_seq"]);
                        infoPcType.Name = dr[i]["icm_pc_type_name"].ToString();
                        info.Date = (Convert.IsDBNull(dr[i]["order_date"]) ? null : (Nullable<DateTime>)(dr[i]["order_date"]));
                        info.Ref_No = dr[i]["icm_po_ref_no"].ToString();
                        info.Name = dr[i]["icm_po_name"].ToString();
                        infoContact.Name_th = dr[i]["contact_name"].ToString();
                        infoRecordStatus.Name = dr[i]["record_status_name"].ToString();

                        infoPr.InfoPurchaseType = infoPcType;
                        infoBidder.InfoPurchaseRequest = infoPr;
                        infoBidder.InfoMasContact = infoContact;
                        info.InfoBidder = infoBidder;
                        info.InfoRecordStatus = infoRecordStatus;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInfo> GetPurchaseOrderContract(Nullable<int> YearTypeSeq, Nullable<int> Year, Nullable<int> PurchaseMethodSeq
            , Nullable<int> PurchaseTypeSeq, Nullable<int> OperationNameSeq, Nullable<int> Status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInfo> infoList = new List<PurchaseOrderInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + " SELECT po.ICM_PO_SEQ"
                        + " , pr.ICM_PC_TYPE_SEQ"
                        + " , pt.ICM_PC_TYPE_NAME"
                        + " , po.ORDER_DATE"
                        + " , po.ICM_PO_REF_NO"
                        + " , po.ICM_PO_NAME"
                        + " , po.CAN_INSTALLMENT"
                        + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                        + " , rs.RECORD_STATUS_NAME"
                        + " , po.YEAR_TYPE_SEQ"
                        + " , po.BGM_YEAR"
                        + " , po.ICM_PO_TYPE_SEQ"
                        + " , pr.ICM_PC_METHOD_SEQ"
                        + " , po.RECORD_STATUS"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID bid ON bid.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bid.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON po.RECORD_STATUS = rs.RECORD_STATUS"
                        + " UNION ALL"
                        + " SELECT po.ICM_PO_SEQ"
                        + " , pr.ICM_PC_TYPE_SEQ"
                        + " , pt.ICM_PC_TYPE_NAME"
                        + " , po.ORDER_DATE"
                        + " , po.ICM_PO_REF_NO"
                        + " , po.ICM_PO_NAME"
                        + " , po.CAN_INSTALLMENT"
                        + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                        + " , rs.RECORD_STATUS_NAME"
                        + " , po.YEAR_TYPE_SEQ"
                        + " , po.BGM_YEAR"
                        + " , po.ICM_PO_TYPE_SEQ"
                        + " , pr.ICM_PC_METHOD_SEQ"
                        + " , po.RECORD_STATUS"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON po.RECORD_STATUS = rs.RECORD_STATUS"
                        + ") main"
                        + " WHERE ICM_PO_TYPE_SEQ = 2"
                            + " AND CAN_INSTALLMENT = @yes_status"
                            + " AND RECORD_STATUS <> @delete_status";
                strSQL += (YearTypeSeq == null) ? "" : " and YEAR_TYPE_SEQ = " + YearTypeSeq;
                strSQL += (Year == null) ? "" : " and BGM_YEAR = " + Year;
                strSQL += (PurchaseMethodSeq == null) ? "" : " and ICM_PC_METHOD_SEQ = " + PurchaseMethodSeq;
                strSQL += (PurchaseTypeSeq == null) ? "" : " and ICM_PC_TYPE_SEQ = " + PurchaseTypeSeq;
                strSQL += (Status == null) ? "" : " and RECORD_STATUS = '" + Status + "'";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInfo info = new PurchaseOrderInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                        MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                        MasterContactInfo infoContact = new MasterContactInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_po_seq"]);
                        infoPcType.Seq = Convert.ToInt32(dr[i]["icm_pc_type_seq"]);
                        infoPcType.Name = dr[i]["icm_pc_type_name"].ToString();
                        info.Date = (Convert.IsDBNull(dr[i]["order_date"]) ? null : (Nullable<DateTime>)(dr[i]["order_date"]));
                        info.Ref_No = dr[i]["icm_po_ref_no"].ToString();
                        info.Name = dr[i]["icm_po_name"].ToString();
                        infoContact.Name_th = dr[i]["contact_name"].ToString();
                        infoRecordStatus.Name = dr[i]["record_status_name"].ToString();

                        infoPr.InfoPurchaseType = infoPcType;
                        infoBidder.InfoPurchaseRequest = infoPr;
                        infoBidder.InfoMasContact = infoContact;
                        info.InfoBidder = infoBidder;
                        info.InfoRecordStatus = infoRecordStatus;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public PurchaseOrderInfo GetPurchaseOrderDetail(int Seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            PurchaseOrderInfo info = new PurchaseOrderInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", Seq);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + " select po.*"
                            + " , pt.icm_pc_type_seq "
                            + " , pt.icm_pc_type_name "
                            + " , concat(c.icm_con_name_th, coalesce(concat('(', c.icm_con_name_eng, ')'),'')) as contact_name "
                            + " , rs.record_status_name "
                            + " , dt.icm_dur_type_name "
                        + " from icm_purchase_order po "
                            + " inner join icm_bidder b on po.icm_bidder_seq = b.icm_bidder_seq"
                            + " inner join icm_mas_contact c on b.icm_con_seq = c.icm_con_seq"
                            + " inner join icm_purchase_request pr on pr.icm_pr_seq = b.icm_pr_seq"
                            + " left join icm_mas_purchase_type pt on pr.icm_pc_type_seq = pt.icm_pc_type_seq"
                            + " left join icm_sys_duration_type dt on po.icm_dur_type_seq = dt.icm_dur_type_seq"
                            + " inner join sys_record_status rs on po.record_status = rs.record_status "
                        + " UNION ALL "
                        + " select po.*"
                            + " , pt.icm_pc_type_seq "
                            + " , pt.icm_pc_type_name "
                            + " , concat(c.icm_con_name_th, coalesce(concat('(', c.icm_con_name_eng, ')'),'')) as contact_name "
                            + " , rs.record_status_name "
                            + " , dt.icm_dur_type_name "
                        + " from icm_purchase_order po "
                            + " inner join icm_bidder b on po.icm_bidder_seq = b.icm_bidder_seq"
                            + " inner join icm_mas_contact c on b.icm_con_seq = c.icm_con_seq"
                            + " inner join icm_bid bid on bid.icm_bid_seq = b.icm_bid_seq"
                            + " inner join icm_purchase_request pr on pr.icm_pr_seq = bid.icm_pr_seq"
                            + " left join icm_mas_purchase_type pt on pr.icm_pc_type_seq = pt.icm_pc_type_seq"
                            + " left join icm_sys_duration_type dt on po.icm_dur_type_seq = dt.icm_dur_type_seq"
                            + " inner join sys_record_status rs on po.record_status = rs.record_status "
                            //" Left Join icm_sys_progress prog ON po.icm_prog_seq = prog.icm_prog_seq" +
                        + " ) main"
                        + " where icm_po_seq = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    BidderInfo infoBidder = new BidderInfo();
                    PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                    MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                    MasterContactInfo infoContact = new MasterContactInfo();
                    SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                    SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    SysProgressInfo infoProgress = new SysProgressInfo();

                    info.Seq = Convert.ToInt32(dr[0]["icm_po_seq"]);
                    infoBidder.Seq = Convert.ToInt32(dr[0]["icm_bidder_seq"]);
                    infoPcType.Seq = Convert.ToInt32(dr[0]["icm_pc_type_seq"]);
                    infoPcType.Name = dr[0]["icm_pc_type_name"].ToString();
                    info.Date = (Convert.IsDBNull(dr[0]["order_date"]) ? null : (Nullable<DateTime>)(dr[0]["order_date"]));
                    info.Ref_No = dr[0]["icm_po_ref_no"].ToString();
                    info.Name = dr[0]["icm_po_name"].ToString();
                    infoContact.Name_th = dr[0]["contact_name"].ToString();
                    infoRecordStatus.Status = dr[0]["record_status"].ToString();
                    infoRecordStatus.Name = dr[0]["record_status_name"].ToString();
                    info.RequireDate = (Convert.IsDBNull(dr[0]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["REQUIRE_DATE"]));
                    info.RequireDuration = (Convert.IsDBNull(dr[0]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[0]["REQUIRE_DURATION"]));
                    infoDurationType.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_TYPE_SEQ"]));
                    infoDurationType.Name = dr[0]["ICM_DUR_TYPE_NAME"].ToString();
                    info.Descr = dr[0]["icm_po_descr"].ToString();
                    //infoProgress.Name = (dr[0]["icm_prog_name"]).ToString();

                    infoPr.InfoPurchaseType = infoPcType;
                    infoBidder.InfoPurchaseRequest = infoPr;
                    infoBidder.InfoMasContact = infoContact;
                    info.InfoBidder = infoBidder;
                    info.InfoDurationType = infoDurationType;
                    info.InfoProgress = infoProgress;
                    info.InfoRecordStatus = infoRecordStatus;
                }
                    return info;
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<PurchaseOrderInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE icm_purchase_order"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE icm_po_seq IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string Insert(PurchaseOrderInfo info,IList<PurchaseOrderInstallmentItemInfo> infoListInstallItem ,IList<PurchaseOrderCommitteeInfo> infoListCommittee,IList<PurchaseOrderPenaltyInfo> infoListPenalty,IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            PurchaseOrderInstallmentDAL dalPoInstallment = new PurchaseOrderInstallmentDAL();
            PurchaseOrderInstallmentItemDAL dalPoInstallmentItem = new PurchaseOrderInstallmentItemDAL();
            PurchaseOrderCommitteeDAL dalPoCommittee = new PurchaseOrderCommitteeDAL();
            PurchaseOrderPenaltyDAL dalPoPenalty = new PurchaseOrderPenaltyDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@icm_bidder_seq", info.InfoBidder.Seq);
                dbManager.AddParameters(1, "@order_date", info.Date);
                dbManager.AddParameters(2, "@icm_po_name", info.Name);
                dbManager.AddParameters(3, "@require_duration", info.RequireDuration);
                dbManager.AddParameters(4, "@icm_dur_type", info.InfoDurationType.Seq);
                dbManager.AddParameters(5, "@require_date", info.RequireDate);
                dbManager.AddParameters(6, "@icm_po_descr", info.Descr);
                dbManager.AddParameters(7, "@can_installment", info.CanInstallment);
                dbManager.AddParameters(8, "@year_type_seq", info.InfoYearType.Seq);
                dbManager.AddParameters(9, "@bgm_year", info.BgmYear);
                dbManager.AddParameters(10, "@emp_seq", info.InfoOrderEmp.Seq);
                dbManager.AddParameters(11, "@job_site_seq", info.InfoOrderJobSite.Seq);
                dbManager.AddParameters(12, "@record_status", info.InfoRecord.InfoStatus.Status);
                dbManager.AddParameters(13, "@request_month", info.Date.Value.Month);
                dbManager.AddParameters(14, "@request_year", info.Date.Value.Year);
                dbManager.AddParameters(15, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(16, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(17, "@po_type_seq", info.InfoPoType.Seq);

                strSQL = "INSERT INTO ICM_PURCHASE_ORDER("
                            + "ICM_BIDDER_SEQ"
                            + ", ORDER_DATE"
                            + ", ORDER_EMP_SEQ"
                            + ", ORDER_JOB_SITE_SEQ"
                            + ", ICM_PO_NAME"
                            + ", ICM_PO_REF_NO" 
                            + ", REQUIRE_DURATION"
                            + ", ICM_DUR_TYPE_SEQ"
                            + ", REQUIRE_DATE"
                            + ", ICM_PO_DESCR" 
                            + ", CAN_INSTALLMENT" 
                            + ", YEAR_TYPE_SEQ"
                            + ", BGM_YEAR"
                            + ", RECORD_STATUS"
                            + ", ICM_PO_TYPE_SEQ" 
                            + ", CREATED_BY"
                            + ", CREATED_WHEN) "
                        + " VALUES ("
                            + " @icm_bidder_seq"
                                + ", @order_date"
                                + ", @emp_seq"
                                + ", @job_site_seq"
                                + ", @icm_po_name"
                                + ", CONCAT('PO-', @request_year + 543, right(concat('00',@request_month),2), '-'"
                                    + ", (select right("
                                        + " concat('0000'"
                                            + ", coalesce("
                                                + "right("
                                                    + "(select max(sub.icm_po_ref_no)"
                                                    + " from icm_purchase_order sub"
                                                    + " where month(sub.order_date) = @request_month"
                                                        + " and year(sub.order_date) = @request_year"
                                                    + ")"
                                                + ", 4)"
                                            + ", 0)"
                                        + " + 1)"
                                    + ",4)"
                                + "))"
                                + ", @require_duration"
                                + ", @icm_dur_type"
                                + ", @require_date"
                                + ", @icm_po_descr" 
                                + ", @can_installment" 
                                + ", @year_type_seq" 
                                + ", @bgm_year"
                                + ", @record_status"
                                + ", @po_type_seq"
                                + ", @created_by" 
                                + ", @created_when)";
                 
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                      + " FROM ICM_PURCHASE_ORDER";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                // Insert [icm_purchase_order_instalment] -------------------------------------------------
                PurchaseOrderInstallmentInfo infoInstallment = new PurchaseOrderInstallmentInfo();
                PurchaseOrderInfo infoPurchaseOrder = new PurchaseOrderInfo();

                infoPurchaseOrder.Seq = lastSeq;
                infoInstallment.InfoPurchaseOrder = infoPurchaseOrder;
                infoInstallment.InstallNumber = 1;
                infoInstallment.RequireDuration = info.RequireDuration;
                infoInstallment.InfoDurationType = info.InfoDurationType;
                infoInstallment.RequireDate = info.RequireDate;
                infoInstallment.InfoRecord = info.InfoRecord;
                int installmentSeq = dalPoInstallment.Insert(infoInstallment, dbManager);

                //Insert [icm_purchase_order_instalment_item] --------------------------------------------
                dalPoInstallmentItem.Insert(infoListInstallItem, installmentSeq, info, dbManager);

                // Insert [icm_purchase_order_committee] ------------------------------------------------
                dalPoCommittee.Insert(infoListCommittee, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_penalty] -------------------------------------------------
                dalPoPenalty.Insert(infoListPenalty, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_file] ----------------------------------------------------
                dalDocument.InsertFileToDB(infoListFile, "ICM_PURCHASE_ORDER_FILE", "ICM_PO_SEQ", lastSeq, dbManager);

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);

                strSQL = "SELECT ICM_PO_REF_NO"
                    + " FROM ICM_PURCHASE_ORDER"
                    + " WHERE ICM_PO_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public string InsertWithInstallment(PurchaseOrderInfo info, IList<PurchaseOrderCommitteeInfo> infoListCommittee, IList<PurchaseOrderPenaltyInfo> infoListPenalty, IList<PurchaseOrderInstallmentInfo> infoListInstallment, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            PurchaseOrderInstallmentDAL dalPoInstallment = new PurchaseOrderInstallmentDAL();
            PurchaseOrderInstallmentItemDAL dalPoInstallmentItem = new PurchaseOrderInstallmentItemDAL();
            PurchaseOrderCommitteeDAL dalPoCommittee = new PurchaseOrderCommitteeDAL();
            PurchaseOrderPenaltyDAL dalPoPenalty = new PurchaseOrderPenaltyDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@icm_bidder_seq", info.InfoBidder.Seq);
                dbManager.AddParameters(1, "@order_date", info.Date);
                dbManager.AddParameters(2, "@icm_po_name", info.Name);
                dbManager.AddParameters(3, "@require_duration", info.RequireDuration);
                dbManager.AddParameters(4, "@icm_dur_type", info.InfoDurationType.Seq);
                dbManager.AddParameters(5, "@require_date", info.RequireDate);
                dbManager.AddParameters(6, "@icm_po_descr", info.Descr);
                dbManager.AddParameters(7, "@can_installment", info.CanInstallment);
                dbManager.AddParameters(8, "@year_type_seq", info.InfoYearType.Seq);
                dbManager.AddParameters(9, "@bgm_year", info.BgmYear);
                dbManager.AddParameters(10, "@emp_seq", info.InfoOrderEmp.Seq);
                dbManager.AddParameters(11, "@job_site_seq", info.InfoOrderJobSite.Seq);
                dbManager.AddParameters(12, "@record_status", info.InfoRecord.InfoStatus.Status);
                dbManager.AddParameters(13, "@request_month", info.Date.Value.Month);
                dbManager.AddParameters(14, "@request_year", info.Date.Value.Year);
                dbManager.AddParameters(15, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(16, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(17, "@po_type_seq", info.InfoPoType.Seq);

                strSQL = "INSERT INTO ICM_PURCHASE_ORDER("
                            + "ICM_BIDDER_SEQ"
                            + ", ORDER_DATE"
                            + ", ORDER_EMP_SEQ"
                            + ", ORDER_JOB_SITE_SEQ"
                            + ", ICM_PO_NAME"
                            + ", ICM_PO_REF_NO"
                            + ", REQUIRE_DURATION"
                            + ", ICM_DUR_TYPE_SEQ"
                            + ", REQUIRE_DATE"
                            + ", ICM_PO_DESCR"
                            + ", CAN_INSTALLMENT"
                            + ", YEAR_TYPE_SEQ"
                            + ", BGM_YEAR"
                            + ", RECORD_STATUS"
                            + ", ICM_PO_TYPE_SEQ"
                            + ", CREATED_BY"
                            + ", CREATED_WHEN) "
                        + " VALUES ("
                            + " @icm_bidder_seq"
                                + ", @order_date"
                                + ", @emp_seq"
                                + ", @job_site_seq"
                                + ", @icm_po_name"
                                + ", CONCAT('PO-', @request_year + 543, right(concat('00',@request_month),2), '-'"
                                    + ", (select right("
                                        + " concat('0000'"
                                            + ", coalesce("
                                                + "right("
                                                    + "(select max(sub.icm_po_ref_no)"
                                                    + " from icm_purchase_order sub"
                                                    + " where month(sub.order_date) = @request_month"
                                                        + " and year(sub.order_date) = @request_year"
                                                    + ")"
                                                + ", 4)"
                                            + ", 0)"
                                        + " + 1)"
                                    + ",4)"
                                + "))"
                                + ", @require_duration"
                                + ", @icm_dur_type"
                                + ", @require_date"
                                + ", @icm_po_descr"
                                + ", @can_installment"
                                + ", @year_type_seq"
                                + ", @bgm_year"
                                + ", @record_status"
                                + ", @po_type_seq"
                                + ", @created_by"
                                + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                      + " FROM ICM_PURCHASE_ORDER";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                // Insert [icm_purchase_order_instalment] -------------------------------------------------
                for (int i = 0; i < infoListInstallment.Count; i++)
                {
                    PurchaseOrderInstallmentInfo infoInstallment = new PurchaseOrderInstallmentInfo();
                    PurchaseOrderInfo infoPurchaseOrder = new PurchaseOrderInfo();

                    infoPurchaseOrder.Seq = lastSeq;
                    infoInstallment.InfoPurchaseOrder = infoPurchaseOrder;
                    infoInstallment.InstallNumber = infoListInstallment[i].InstallNumber;
                    infoInstallment.RequireDuration = infoListInstallment[i].RequireDuration;
                    infoInstallment.InfoDurationType = infoListInstallment[i].InfoDurationType;
                    infoInstallment.RequireDate = infoListInstallment[i].RequireDate;
                    infoInstallment.InfoRecord = info.InfoRecord;
                    int installmentSeq = dalPoInstallment.Insert(infoInstallment, dbManager);

                    //Insert [icm_purchase_order_instalment_item] --------------------------------------------
                    dalPoInstallmentItem.Insert(infoListInstallment[i].InfoListItem, installmentSeq, info, dbManager);
                }

                // Insert [icm_purchase_order_committee] ------------------------------------------------
                dalPoCommittee.Insert(infoListCommittee, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_penalty] -------------------------------------------------
                dalPoPenalty.Insert(infoListPenalty, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_file] ----------------------------------------------------
                dalDocument.InsertFileToDB(infoListFile, "ICM_PURCHASE_ORDER_FILE", "ICM_PO_SEQ", lastSeq, dbManager);

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);

                strSQL = "SELECT ICM_PO_REF_NO"
                    + " FROM ICM_PURCHASE_ORDER"
                    + " WHERE ICM_PO_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public string InsertContract(PurchaseOrderInfo info, IList<PurchaseOrderCommitteeInfo> infoListCommittee, IList<PurchaseOrderPenaltyInfo> infoListPenalty, IList<PurchaseOrderInstallmentInfo> infoListInstallment, IList<PurchaseOrderCollateralInfo> infoListCollateral, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            PurchaseOrderInstallmentDAL dalPoInstallment = new PurchaseOrderInstallmentDAL();
            PurchaseOrderInstallmentItemDAL dalPoInstallmentItem = new PurchaseOrderInstallmentItemDAL();
            PurchaseOrderCommitteeDAL dalPoCommittee = new PurchaseOrderCommitteeDAL();
            PurchaseOrderPenaltyDAL dalPoPenalty = new PurchaseOrderPenaltyDAL();
            PurchaseOrderCollateralDAL dalPoCollateral = new PurchaseOrderCollateralDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@icm_bidder_seq", info.InfoBidder.Seq);
                dbManager.AddParameters(1, "@order_date", info.Date);
                dbManager.AddParameters(2, "@icm_po_name", info.Name);
                dbManager.AddParameters(3, "@require_duration", info.RequireDuration);
                dbManager.AddParameters(4, "@icm_dur_type", info.InfoDurationType.Seq);
                dbManager.AddParameters(5, "@require_date", info.RequireDate);
                dbManager.AddParameters(6, "@icm_po_descr", info.Descr);
                dbManager.AddParameters(7, "@can_installment", info.CanInstallment);
                dbManager.AddParameters(8, "@year_type_seq", info.InfoYearType.Seq);
                dbManager.AddParameters(9, "@bgm_year", info.BgmYear);
                dbManager.AddParameters(10, "@emp_seq", info.InfoOrderEmp.Seq);
                dbManager.AddParameters(11, "@job_site_seq", info.InfoOrderJobSite.Seq);
                dbManager.AddParameters(12, "@record_status", info.InfoRecord.InfoStatus.Status);
                dbManager.AddParameters(13, "@request_month", info.Date.Value.Month);
                dbManager.AddParameters(14, "@request_year", info.Date.Value.Year);
                dbManager.AddParameters(15, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(16, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(17, "@po_type_seq", info.InfoPoType.Seq);

                strSQL = "INSERT INTO ICM_PURCHASE_ORDER("
                            + "ICM_BIDDER_SEQ"
                            + ", ORDER_DATE"
                            + ", ORDER_EMP_SEQ"
                            + ", ORDER_JOB_SITE_SEQ"
                            + ", ICM_PO_NAME"
                            + ", ICM_PO_REF_NO"
                            + ", REQUIRE_DURATION"
                            + ", ICM_DUR_TYPE_SEQ"
                            + ", REQUIRE_DATE"
                            + ", ICM_PO_DESCR"
                            + ", CAN_INSTALLMENT"
                            + ", YEAR_TYPE_SEQ"
                            + ", BGM_YEAR"
                            + ", RECORD_STATUS"
                            + ", ICM_PO_TYPE_SEQ"
                            + ", CREATED_BY"
                            + ", CREATED_WHEN) "
                        + " VALUES ("
                            + " @icm_bidder_seq"
                                + ", @order_date"
                                + ", @emp_seq"
                                + ", @job_site_seq"
                                + ", @icm_po_name"
                                + ", CONCAT('PO-', @request_year + 543, right(concat('00',@request_month),2), '-'"
                                    + ", (select right("
                                        + " concat('0000'"
                                            + ", coalesce("
                                                + "right("
                                                    + "(select max(sub.icm_po_ref_no)"
                                                    + " from icm_purchase_order sub"
                                                    + " where month(sub.order_date) = @request_month"
                                                        + " and year(sub.order_date) = @request_year"
                                                    + ")"
                                                + ", 4)"
                                            + ", 0)"
                                        + " + 1)"
                                    + ",4)"
                                + "))"
                                + ", @require_duration"
                                + ", @icm_dur_type"
                                + ", @require_date"
                                + ", @icm_po_descr"
                                + ", @can_installment"
                                + ", @year_type_seq"
                                + ", @bgm_year"
                                + ", @record_status"
                                + ", @po_type_seq"
                                + ", @created_by"
                                + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                      + " FROM ICM_PURCHASE_ORDER";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                // Insert [icm_purchase_order_instalment] -------------------------------------------------
                for (int i = 0; i < infoListInstallment.Count; i++)
                {
                    PurchaseOrderInstallmentInfo infoInstallment = new PurchaseOrderInstallmentInfo();
                    PurchaseOrderInfo infoPurchaseOrder = new PurchaseOrderInfo();

                    infoPurchaseOrder.Seq = lastSeq;
                    infoInstallment.InfoPurchaseOrder = infoPurchaseOrder;
                    infoInstallment.InstallNumber = infoListInstallment[i].InstallNumber;
                    infoInstallment.RequireDuration = infoListInstallment[i].RequireDuration;
                    infoInstallment.InfoDurationType = infoListInstallment[i].InfoDurationType;
                    infoInstallment.RequireDate = infoListInstallment[i].RequireDate;
                    infoInstallment.InfoRecord = info.InfoRecord;
                    int installmentSeq = dalPoInstallment.Insert(infoInstallment, dbManager);

                    //Insert [icm_purchase_order_instalment_item] --------------------------------------------
                    dalPoInstallmentItem.Insert(infoListInstallment[i].InfoListItem, installmentSeq, info, dbManager);
                }

                // Insert [icm_purchase_order_committee] ------------------------------------------------
                dalPoCommittee.Insert(infoListCommittee, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_penalty] -------------------------------------------------
                dalPoPenalty.Insert(infoListPenalty, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_collateral] -------------------------------------------------
                dalPoCollateral.Insert(infoListCollateral, lastSeq, info, dbManager);

                // Insert [icm_purchase_order_file] ----------------------------------------------------
                dalDocument.InsertFileToDB(infoListFile, "ICM_PURCHASE_ORDER_FILE", "ICM_PO_SEQ", lastSeq, dbManager);

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);

                strSQL = "SELECT ICM_PO_REF_NO"
                    + " FROM ICM_PURCHASE_ORDER"
                    + " WHERE ICM_PO_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public void Update(PurchaseOrderInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@icm_po_seq", info.Seq);
                dbManager.AddParameters(1, "@order_date", info.Date);
                dbManager.AddParameters(2, "@icm_po_name", info.Name);
                dbManager.AddParameters(3, "@require_duration", info.RequireDuration);
                dbManager.AddParameters(4, "@icm_dur_type", info.InfoDurationType.Seq);
                dbManager.AddParameters(5, "@require_date", info.RequireDate);
                dbManager.AddParameters(6, "@icm_po_descr", info.Descr);
                dbManager.AddParameters(7, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(8, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE icm_purchase_order"
                    + " SET order_date = @order_date"
                    + ", icm_po_name = @icm_po_name"
                    + ", require_duration = @require_duration"
                    + ", icm_dur_type_seq = @icm_dur_type"
                    + ", require_date = @require_date"
                    + ", icm_po_descr = @icm_po_descr"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE icm_po_seq = @icm_po_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@icm_po_seq", info.Seq);
                dbManager.AddParameters(1, "@icm_dur_type", info.InfoDurationType.Seq);
                dbManager.AddParameters(2, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PURCHASE_ORDER_INSTALLMENT"
                   + " SET ICM_DUR_TYPE_SEQ = @icm_dur_type"
                   + ", UPDATED_BY = @updated_by"
                   + ", UPDATED_WHEN = @updated_when"
                   + " WHERE ICM_PO_SEQ = @icm_po_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePurchaseOrderStatus(int po_seq, string Status)
        {
            string strSQL;
            PurchaseRequsteRouteDAL dalRoute = new PurchaseRequsteRouteDAL();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_po_seq", po_seq);
                dbManager.AddParameters(1, "@record_status", Status);

                strSQL = "UPDATE ICM_PURCHASE_ORDER SET RECORD_STATUS = @record_status WHERE ICM_PO_SEQ = @icm_po_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string UpdateTotalAndAmountConfirm(PurchaseOrderInfo info, IList<PrePurchaseRequestItemInfo> infoListPprItem, IList<PurchaseOrderRouteInfo> infoListRoute)
        {
            PrePurchaseRequestItemDAL dalPpritem = new PrePurchaseRequestItemDAL();
            string returnTxt = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dalPpritem.UpdateAmountForPurchaseOrder(infoListPprItem, dbManager);
                returnTxt = dalPpritem.CheckPurchaseRequestMoneyForPurchaseOrder(Convert.ToInt32(info.Seq), dbManager);

                if (returnTxt != "0")
                {
                    dbManager.RollBackTransaction();
                    return "กรุณาตรวจสอบจำนวนและวงเงิน";
                }

                Confirm(info, infoListRoute, dbManager);
                UpdateDeliveryDate(info, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return returnTxt;
        }

        public void Confirm(PurchaseOrderInfo info, IList<PurchaseOrderRouteInfo> infoListRoute, IDBManager dbManagerTrans)
        {
            string strSQL;
            PurchaseOrderRouteDAL dalRoute = new PurchaseOrderRouteDAL();
            try
            {
                dbManagerTrans.CreateParameters(6);

                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@confirm_emp_seq", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@confirm_date", info.ConfirmDate);
                dbManagerTrans.AddParameters(3, "@date_start", info.DateStartCount);
                dbManagerTrans.AddParameters(4, "@delivery_date", info.DeliveryDate);
                dbManagerTrans.AddParameters(5, "@record_status", SystemInfo._ApproveRecordStatus);// SystemInfo._ProcessRecordStatus);

                strSQL = "UPDATE ICM_PURCHASE_ORDER"
                    + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                    + ", CONFIRM_DATE = @confirm_date"
                    + ", DATE_START_COUNT = @date_start"
                    + ", DELIVERY_DATE = @delivery_date"
                    + ", RECORD_STATUS = @record_status"
                    + " WHERE ICM_PO_SEQ = @seq";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManagerTrans.CreateParameters(4);
                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@created_by", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@created_when", DateTime.Now);
                dbManagerTrans.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                strSQL = "INSERT INTO BGM_PROJECT_OPERATION_REPORT"
                    + "(DESCR, BGM_PROJ_OPER_EXP_SEQ, ICM_PO_INSTALL_ITEM_SEQ, USED_AMOUNT, CAL_AMOUNT, RPT_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + "(SELECT DISTINCT (select icm_po_name from icm_purchase_order where icm_po_seq = @seq), pri.BGM_PROJ_OPER_EXP_SEQ, ii.ICM_PO_INSTALL_ITEM_SEQ, COALESCE(ii.NET_AMOUNT,0)"
                        + ", COALESCE(- (ii.NET_AMOUNT),0), 5, @record_status, @created_by, @created_when"
                    + " FROM ICM_PURCHASE_ORDER_INSTALLMENT_ITEM ii"
                        + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT oi ON ii.ICM_PO_INSTALL_SEQ = oi.ICM_PO_INSTALL_SEQ"
                        + " INNER JOIN ICM_BIDDER_QUOTATION q ON ii.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ"
                        + " INNER JOIN ICM_PRE_PURCHASE_REQUEST_ITEM pri ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ"
                    + " WHERE oi.ICM_PO_SEQ = @seq)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManagerTrans.CreateParameters(4);
                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@created_by", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@created_when", DateTime.Now);
                dbManagerTrans.AddParameters(3, "@yes_status", SystemInfo._YesStatus);

                strSQL = "UPDATE BGM_PROJECT_OPERATION_REPORT"
                    + " SET CAL_AMOUNT = 0"
                    + ", UPDATED_BY = @created_by"
                    + ", UPDATED_WHEN = @created_when"
                    + " WHERE ICM_PR_ITEM_SEQ IS NOT NULL"
                        + " AND ICM_PR_ITEM_SEQ IN"
                            + " (select pr.icm_pr_item_seq"
                            + " from icm_purchase_order_installment_item oii"
                                + " inner join icm_purchase_order_installment oi on oii.icm_po_install_seq = oi.icm_po_install_seq"
                                + " inner join icm_bidder_quotation q on oii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                                + " inner join icm_purchase_request_item pr on q.icm_pr_item_seq = pr.icm_pr_item_seq"
                            + " where oi.icm_po_seq = @seq"
                                + " and q.selected = @yes_status)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoConfirmEmp.Seq, dbManagerTrans);
            }
            catch
            {
                throw;
            }
        }

        public void UpdateDeliveryDate(PurchaseOrderInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                dbManagerTrans.CreateParameters(1);

                dbManagerTrans.AddParameters(0, "@po_seq", info.Seq);

                strSQL = "CALL P_Auto_Update_Delivery_Date_Installment(@po_seq)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
        }
    }
}
